﻿using System;
using SmartSiteManagementSystem.IRepository.Admin;
using SmartSiteManagementSystem.Models.Admin;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;

namespace SmartSiteManagementSystem.Repository.Admin
{
    public class AdminUserGroupRelationRepository :IAdminUserGroupRelationInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
create table `admin_user_group_relation`(
	`user_relat_id` int not null auto_increment primary key,
	`user_group_id` int not null,
	`admin_user_id`  int not null
    )default charset=utf8;
         */
        #endregion
        private IDbConnection _readConn;
        private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from admin_user_group_relation";
            using (_writeConn = DBConectionHelper.GetInstance().GetWriteConn())
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("user_relat_id"))
                    {
                        pars.Add("user_relat_id", param["user_relat_id"]);

                        whereCondition.Append(" user_relat_id =@user_relat_id");
                        parCount++;
                    }
                    if (param.ContainsKey("user_group_id"))
                    {
                        pars.Add("user_group_id", param["user_group_id"]);
                        if (parCount > 0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("user_group_id =@user_group_id");
                        parCount++;
                    }
                    if (param.ContainsKey("admin_user_id"))
                    {
                        pars.Add("admin_user_id", param["admin_user_id"]);
                        if(parCount>0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("admin_user_id =@admin_user_id");
                        parCount++;
                    }
                   
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if(backInt>0)
                {
                    back = true;
                }
            }
            return back;
        }



        public AdminGroupInfo Get(Hashtable param)
        {
            AdminGroupInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select group_id as AdminUserGroupId, group_name as AdminUserGroupName,code as Code,email as Email,sort_order as SortOrder,is_stop as StopFlag,is_deleted as DeleteFlag,remark as Remark,create_time as CreateTime,update_time as UpdateTime from admin_group";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("group_id"))
                    {
                        pars.Add("group_id", param["group_id"]);
                        whereCondition.Append(" and group_id =@group_id");
                    }
                    if (param.ContainsKey("group_name"))
                    {
                        pars.Add("group_name", param["group_name"]);
                        whereCondition.Append(" and group_name =@group_name");
                    }
                    if (param.ContainsKey("code"))
                    {
                        pars.Add("code", param["code"]);
                        whereCondition.Append(" and code =@code");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<AdminGroupInfo>(execSql, pars);
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(user_relat_id) from admin_user_group_relation";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("user_group_id"))
                    {
                        pars.Add("user_group_id", param["user_group_id"]);
                        whereCondition.Append(" and user_group_id =@user_group_id");
                    }
                    if (param.ContainsKey("admin_user_id"))
                    {
                        pars.Add("admin_user_id", param["admin_user_id"]);
                        whereCondition.Append(" and admin_user_id =@admin_user_id");
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<AdminUserGroupRelationInfo> GetList(Hashtable param)
        {
            IList<AdminUserGroupRelationInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select user_relat_id as AdminUserRelatId ,augr.user_group_id as AdminUserGroupId ,admin_user_id as AdminUserId from admin_user_group_relation augr inner join admin_group ag   on augr.user_group_id=ag.group_id ";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("admin_user_id"))
                    {
                        pars.Add("admin_user_id", param["admin_user_id"]);
                        whereCondition.Append(" and augr.admin_user_id =@admin_user_id");
                    }
                    if (param.ContainsKey("is_deleted_stop"))
                    {
                        whereCondition.Append(" and ag.is_deleted = 0 and ag.is_stop = 0");
                    }
                }
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<AdminUserGroupRelationInfo>(execSql, pars).AsList<AdminUserGroupRelationInfo>();
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(AdminUserGroupRelationInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO admin_user_group_relation(user_group_id,admin_user_id)VALUES(@user_group_id,@admin_user_id);select last_insert_id();";
            #endregion
            using (_writeConn = DBConectionHelper.GetInstance().GetWriteConn())
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    user_group_id = obj.AdminUserGroupId,
                    admin_user_id = obj.AdminUserId
                });
            }
            return back;
        }

    }
}

